﻿using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;

namespace PCSComPayroll.DS
{
    public class PayrollReportDS
    {        
        public DataTable GetAllItems()
        {
            OleDbConnection cn = null;
            try
            {
                cn = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                DataTable dtbItem = new DataTable();
                string strSql = " SELECT	DISTINCT '1' AS 'Level', "
                    + " ITM_Category.Code AS CategoryCode, "
                    + " ITM_Product.Code AS 'Part No.', "
                    + " ITM_Product.Description AS 'Part Name', "
                    + " ITM_Product.Revision AS 'Model', "
                    + " MST_UnitOfMeasure.Code AS 'Stock UM', "
                    + " PRO_ProductionLine.Code AS 'ProcessCode', "
                    + " ITM_BOM.Quantity AS 'BOM Qty', "
                    + " MST_Party.Code AS 'Supplier',"
                    + " ISNULL((SELECT SUM(ISNULL(TotalTime,0))"
                    + " FROM PAY_ProductionNorm"
                    + " WHERE ComponentID = ITM_Product.ProductID"
                    + " AND ProductionLineID = ITM_Product.ProductionLineID"
                    + " ), 0) AS ProductionNorm,"
                    + " ITM_Product.ProductID, ITM_BOM.ProductID AS 'ParentID',"
                    + " ITM_Product.MakeItem"
                    + " FROM ITM_Product JOIN ITM_BOM"
                    + " ON ITM_Product.ProductID = ITM_BOM.ComponentID"
                    + " LEFT JOIN PRO_ProductionLine ON ITM_Product.ProductionLineID = PRO_ProductionLine.ProductionLineID"
                    + " LEFT JOIN MST_Party"
                    + " ON ITM_Product.PrimaryVendorID = MST_Party.PartyID"
                    + " LEFT JOIN ITM_Category"
                    + " ON ITM_Product.CategoryID = ITM_Category.CategoryID"
                    + " JOIN MST_UnitOfMeasure"
                    + " ON MST_UnitOfMeasure.UnitOfMeasureID = ITM_Product.StockUMID"
                    + " WHERE ISNULL(ITM_Product.MakeItem,0) = 1"
                    + " ORDER BY ITM_Product.Code, ITM_Product.Description, ITM_Product.Revision";
                OleDbDataAdapter odad = new OleDbDataAdapter(strSql, cn);
                odad.Fill(dtbItem);
                return dtbItem;
            }
            finally
            {
                if (cn != null)
                    if (cn.State != ConnectionState.Closed)
                        cn.Close();
            }
        }

        public DataTable GetOneItem(int pintProductID)
        {
            OleDbConnection cn = null;
            try
            {
                cn = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                DataTable dtbItem = new DataTable();
                string strSql = " SELECT	DISTINCT '1' AS 'Level', "
                    + " ITM_Category.Code AS CategoryCode, "
                    + " ITM_Product.Code AS 'Part No.', "
                    + " ITM_Product.Description AS 'Part Name', "
                    + " ITM_Product.Revision AS 'Model', "
                    + " MST_UnitOfMeasure.Code AS 'Stock UM', "
                    + " PRO_ProductionLine.Code AS 'ProcessCode', "
                    + " CAST(NULL AS decimal(18,5)) AS 'BOM Qty', "
                    + " MST_Party.Code AS 'Supplier',"
                    + " ISNULL((SELECT SUM(ISNULL(TotalTime,0))"
                    + " FROM PAY_ProductionNorm"
                    + " WHERE ComponentID = ITM_Product.ProductID"
                    + " AND ProductionLineID = ITM_Product.ProductionLineID"
                    + " ), 0) AS ProductionNorm,"
                    + " ITM_Product.ProductID, ITM_Product.ProductID AS 'ParentID',"
                    + " ITM_Product.MakeItem"
                    + " FROM ITM_Product"
                    + " LEFT JOIN PRO_ProductionLine ON ITM_Product.ProductionLineID = PRO_ProductionLine.ProductionLineID"
                    + " LEFT JOIN MST_Party"
                    + " ON ITM_Product.PrimaryVendorID = MST_Party.PartyID"
                    + " LEFT JOIN ITM_Category"
                    + " ON ITM_Product.CategoryID = ITM_Category.CategoryID"
                    + " JOIN MST_UnitOfMeasure"
                    + " ON MST_UnitOfMeasure.UnitOfMeasureID = ITM_Product.StockUMID"
                    + " WHERE ITM_Product.ProductID =  " + pintProductID;
                OleDbDataAdapter odad = new OleDbDataAdapter(strSql, cn);
                odad.Fill(dtbItem);
                return dtbItem;
            }
            finally
            {
                if (cn != null)
                    if (cn.State != ConnectionState.Closed)
                        cn.Close();
            }
        }
    }
}
